package com.xhs.hawaii.common.utils.excel;

//import com.xhs.uranus.common.utils.StringUtils;
import com.xhs.hawaii.common.utils.StringUtils;
//import com.xhs.uranus.common.utils.excel.ColumnInfo.ColumnType;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author chenqunhui
 */
@Slf4j
public class ExcelUtil {

    public static Workbook transforWorkbook(InputStream in) {
        BufferedInputStream bufIn = new BufferedInputStream(in);
        Workbook wb = null;
        try {
            wb = new XSSFWorkbook(bufIn);
        } catch (IOException e) {
            return null;
        } finally {
            if (null != bufIn) {
                try {
                    bufIn.close();
                } catch (IOException e) {

                }
            }
        }
        return wb;
    }

    /**
     * 将文件转换为Excel Workbook
     *
     * @param file
     * @return
     * @throws FileNotFoundException
     */
    public static Workbook transforWorkbook(File file) {
        try {
            return transforWorkbook(new FileInputStream(file));
        } catch (FileNotFoundException e) {
            return null;
        }
    }


//	public static ExcelContext getExcelContext(File file){
//		return getExcelContext(transforWorkbook(file));
//	}

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getStringValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        int cellType = cell.getCellType();

        String value = null;
        switch (cellType) {
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                DecimalFormat df = new DecimalFormat("0");
                value = df.format(cell.getNumericCellValue());
                value = Double.toString(cell.getNumericCellValue());
            /*CellValue cellValue = evaluator.evaluate(cell);
            if(cellValue != null) {
				value = cellValue.formatAsString();
			}else {
				value = "";
			}*/
                break;
            case Cell.CELL_TYPE_NUMERIC:
                DecimalFormat df1 = new DecimalFormat("#.##");
                df1.setRoundingMode(RoundingMode.DOWN);
                value = df1.format(cell.getNumericCellValue());
			
			/*short format = cell.getCellStyle().getDataFormat();  
		    SimpleDateFormat sdf = null;
		    if(format == 0) {//数字
		    	value = String.valueOf(cell.getNumericCellValue());
		    }else {
		    	if(format == 14 || format == 31 || format == 57 || format == 58){//日期  
			        sdf = new SimpleDateFormat("yyyy-MM-dd");  
			    }else if (format == 20 || format == 32) {//时间
			        sdf = new SimpleDateFormat("HH:mm");  
			    }else {
			    	sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");  
			    }
		    	Date date = cell.getDateCellValue(); 
				value = sdf.format(date);
		    }*/
                break;
            // 如果当前Cell的Type为STRING
            case Cell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                value = cell.getStringCellValue().replaceAll("'", "''");
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            default:
                value = String.valueOf(cell.getStringCellValue());

        }

        return value;
    }

    public static Cell createCell(Row row, int cellNum, String value) {
        Cell cell = row.createCell(cellNum, Cell.CELL_TYPE_STRING);
        cell.setCellValue(getNotNullStr(value));
        return cell;
    }

    public static Cell createCell(Row row, int cellNum, Double value) {
        Cell cell = row.createCell(cellNum, Cell.CELL_TYPE_NUMERIC);
        cell.setCellValue(value);
        return cell;
    }

    public static String getNotNullStr(Object obj) {
        return (obj == null) ? "" : String.valueOf(obj);
    }


    /**
     * 设置列宽
     *
     * @param sheet
     * @param cellIndex
     * @param width     正常值为20【             】
     */
    public static void setCellWidth(Sheet sheet, int cellIndex, int width) {
        sheet.setColumnWidth(cellIndex, 256 * width + 184);
    }

    /**
     * 生成excel表Ø
     *
     * @param sheets 工作表信息
     * @throws IllegalArgumentException
     */
    public static Workbook list2Excel(Workbook workbook, ArrayList<SheetInfo> sheets, String identity) throws IllegalArgumentException, IOException {
        CellStyle intStyle = workbook.createCellStyle();
        intStyle.setDataFormat(workbook.createDataFormat().getFormat("0"));

        CellStyle decimal2Style = workbook.createCellStyle();
        decimal2Style.setDataFormat(workbook.createDataFormat().getFormat("0.00"));

        CellStyle decimal4Style = workbook.createCellStyle();
        decimal4Style.setDataFormat(workbook.createDataFormat().getFormat("0.0000"));

        Font font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        for (SheetInfo sheetInfo : sheets) {
            Sheet sheet = workbook.getSheet(sheetInfo.getSheetName());
            if (sheet == null) {
                workbook.createSheet(sheetInfo.getSheetName());
            }
            int startRow = sheetInfo.getStartRow();  // 开始行号
            Row rowHead = sheet.createRow(startRow);

            List<ColumnInfo> columnInfos = sheetInfo.getColumns();
            for (int i = 0; i < columnInfos.size(); i++) {
                Cell cell = rowHead.createCell(i, Cell.CELL_TYPE_STRING);
                cell.setCellValue(columnInfos.get(i).getHeaderName());
                CellStyle headerStyle = workbook.createCellStyle();
                headerStyle.setFont(font);
                cell.setCellStyle(headerStyle);
                sheet.setColumnWidth(i, 256 * columnInfos.get(i).getWidth());
            }

            List<Map<String, String>> datas = sheetInfo.getDatas();
            for (int rowNum = 0; rowNum < datas.size(); rowNum++) {
                Row dataRow = sheet.createRow(rowNum + startRow + 1); // header是第一行，因此数据行加(startRow+1)
                Map<String, String> data = datas.get(rowNum);
                for (int colmunNum = 0; colmunNum < columnInfos.size(); colmunNum++) {
                    ColumnInfo columnInfo = columnInfos.get(colmunNum);
                    Cell cell = dataRow.createCell(colmunNum);
                    if (ColumnInfo.ColumnType.NUMERIC.equals(columnInfo.getColumnType())) { // 对于数值类型，要设置格式
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(intStyle);
                        cell.setCellValue(Integer.valueOf(data.get(columnInfos.get(colmunNum).getFieldName())));
                    } else if (ColumnInfo.ColumnType.DECIMAL2.equals(columnInfo.getColumnType())) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(decimal2Style);
                        cell.setCellValue(Double.valueOf(data.get(columnInfos.get(colmunNum).getFieldName())));
                    } else if (ColumnInfo.ColumnType.DECIMAL4.equals(columnInfo.getColumnType())) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(decimal4Style);
                        cell.setCellValue(Double.valueOf(data.get(columnInfos.get(colmunNum).getFieldName())));
                    } else if (ColumnInfo.ColumnType.LINK_URL.equals(columnInfo.getColumnType())) {
                        CellStyle cellStyle = workbook.createCellStyle();
                        Font hlinkfont = workbook.createFont();
                        hlinkfont.setUnderline(Font.U_SINGLE);
                        hlinkfont.setColor(HSSFColor.BLUE.index);
                        cellStyle.setFont(hlinkfont);
                        Hyperlink hyperlink = workbook.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
                        hyperlink.setAddress(String.valueOf(data.get(columnInfos.get(colmunNum).getFieldName())));
                        cell.setCellValue(StringUtils.defaultString(data.get(columnInfos.get(colmunNum).getFieldName()),""));
                        cell.setHyperlink(hyperlink);
                        cell.setCellStyle(cellStyle);
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(data.get(columnInfos.get(colmunNum).getFieldName()));
                    }
                }
            }
        }

        return workbook;
    }
}
